from datetime import time, datetime

from sqlalchemy.orm import Session

from SQLiteBase import engine, AutoWords

# 根据单词查询
def get_word_by_word(word):
    lower_word = word.lower()
    with Session(engine) as session:
        word_info = session.query(AutoWords).filter(AutoWords.word == lower_word).first()
        if word_info is None:
            return None
        else:
            return word_info.to_dict()

# 保存单词
def save_word(word, pronunciation, meaning, costs):
    lower_word = word.lower()
    current_time = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
    with Session(engine) as session:
        word_info = session.query(AutoWords).filter(AutoWords.word == lower_word).first()
        if word_info is None:
            word_info = AutoWords(
                word=lower_word,
                pronunciation=pronunciation,
                meaning=meaning,
                costs=costs,
                create_time=current_time
            )
            session.add(word_info)
            session.commit()
            return word_info.to_dict()
        else:
            return None


# 保存或者更新单词
def save_or_update_word(word, pronunciation, meaning, costs):
    lower_word = word.lower()
    current_time = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
    with Session(engine) as session:
        word_info = session.query(AutoWords).filter(AutoWords.word == lower_word).first()
        if word_info is None:
            word_info = AutoWords(
                word=lower_word,
                pronunciation=pronunciation,
                meaning=meaning,
                costs=costs,
                create_time=current_time
            )
            session.add(word_info)
            session.commit()
            return word_info.to_dict()
        else:
            word_info.pronunciation = pronunciation
            word_info.meaning = meaning
            word_info.costs = costs
            word_info.create_time = current_time
            session.commit()
            return word_info.to_dict()


# 获取最新一条单词
def get_latest_word():
    with Session(engine) as session:
        word_info = session.query(AutoWords).order_by(AutoWords.create_time.desc()).first()
        if word_info is None:
            return None
        else:
            return word_info.to_dict()


# 获取，当前单词的下一个单词，根据 create_time 排序。
def get_next_word(word):
    lower_word = word.lower()
    with Session(engine) as session:
        # 先查询单词的信息，获取create_time
        word_curr = session.query(AutoWords).filter(AutoWords.word == lower_word).first()
        if word_curr is None:
            return None
        word_info = session.query(AutoWords).filter(AutoWords.create_time < word_curr.create_time).order_by(
            AutoWords.create_time.desc()).first()
        if word_info is None:
            return None
        else:
            return word_info.to_dict()


# 获取，当前单词的上一个单词，根据 create_time 排序。
def get_previous_word(word):
    lower_word = word.lower()
    with Session(engine) as session:
        # 先查询单词的信息，获取create_time
        word_curr = session.query(AutoWords).filter(AutoWords.word == lower_word).first()
        if word_curr is None:
            return None
        word_info = session.query(AutoWords).filter(AutoWords.create_time > word_curr.create_time).order_by(
            AutoWords.create_time.asc()).first()
        if word_info is None:
            return None
        else:
            return word_info.to_dict()
